/*
This file recursively imports xlsx files corresponding to exchange rates from Datastream.
THE EXCHANGE RATE IS: 1 USD = x local currency.
AN INCREASE IN THE EXCHANGE RATE IS AN APPRECIATION OF THE USD WITH RESPECT TO THE LOCAL CURRENCY.
*/

clear all
* variables list for loop
global countries "  australia      belgium   brazil canada        chile    china    colombia        denmark    finland    france        germany     hongkong       india    indonesia    ireland    israel    italy    japan   malaysia    mexico   netherlands         norway  peru    philippines  poland     singapore koreasouth                   southafrica     spain    sweden    switzerland              taiwan    thailand    turkey   unitedkingdom    "
global nonUKcountries "  australia      belgium   brazil canada        chile    china    colombia        denmark    finland    france        germany     hongkong       india    indonesia    ireland    israel    italy    japan   malaysia    mexico   netherlands         norway  peru    philippines  poland     singapore koreasouth                   southafrica     spain    sweden    switzerland              taiwan    thailand    turkey      "

* temporary individual file
foreach sheet of global countries{
	display "`sheet'"
	tempfile tempeq_`sheet'
	
	* import
	qui import excel "$datapath/datastream/exchange_rates_clean.xlsx", clear sheet("`sheet'") cellrange(A6) firstrow 
	* remove NAs
	ds, has(type string) 
	qui foreach v in `r(varlist)' { 
		replace `v' = "" if trim(`v') == "NA" 
	} 
	qui destring _all, replace force // destring
	* nice date format
	qui gen datem = mofd(date)
	format datem %tm
	qui drop date
	qui drop if missing(datem)
	
	qui save "`tempeq_`sheet''", replace
}

* merge
use "`tempeq_unitedkingdom'", clear
foreach sheet of global nonUKcountries {
		qui merge 1:1 datem using "`tempeq_`sheet''", nogenerate
}
// Delete variables without any observation
foreach var of varlist _all {
	capture assert mi(`var')
	 if !_rc {
		drop `var'
	 }
}
drop C

* reshape
qui reshape long USD_ , i(datem) j(country) string
sort country datem
rename USD_ exrate
drop if mi(exrate)
bys country (datem) : gen lexrate = exrate[_n-1] // lag
bys country (datem) : gen fexrate = exrate[_n+1] // forward

* save
save "$datapath/Exchange_Rates_exceldata_to_DTA.dta", replace
*end
***********************************************************